package nativejpa.test.jpa.derby;

import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.AfterClass;
import org.junit.Assert;

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.EntityManager;
import java.util.List;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @author <a href="mailto:jfox.young@gmail.com">Young Yang</a>
 * @create 2009-3-20 16:06:47
 */
public abstract class DerbyTestSupport {

    static String TEST_PERSISTENCE_UNIT_NAME = "test_persistence_unit";
    static EntityManagerFactory emf;
    static EntityManagerFactory emf2;
    static EntityManager em;
    static EntityManager em2;

    @BeforeClass
    public static void beforeClass(){
        emf = Persistence.createEntityManagerFactory(TEST_PERSISTENCE_UNIT_NAME);
        Map properties = new HashMap();
        properties.put("akey", "avalue"); // just a fake parameter

        emf2 = Persistence.createEntityManagerFactory(TEST_PERSISTENCE_UNIT_NAME, properties);
        em = emf.createEntityManager();        
        em2 = emf.createEntityManager();

        em.createNativeQuery("CREATE TABLE COUNTRIES(" +
                "COUNTRY VARCHAR(26) NOT NULL, " +
                "COUNTRY_ISO_CODE CHAR(2) NOT NULL, " +
                "REGION VARCHAR(26))").executeUpdate();

        String[] initQueries = {"insert into COUNTRIES values ( 'Albania','AL','Europe')",
                "insert into COUNTRIES values ('Algeria','DZ','North Africa')",
                "insert into COUNTRIES values ('American Samoa','AS','Pacific Islands')",
                "insert into COUNTRIES values ('Angola','AO','Africa')",
                "insert into COUNTRIES values ('Argentina','AR','South America')",
                "insert into COUNTRIES values ('Armenia','AM','Europe')",
                "insert into COUNTRIES values ('Australia','AU','Australia and New Zealand')",
                "insert into COUNTRIES values ('Austria','AT','Europe')",
                "insert into COUNTRIES values ('Azerbaijan','AZ','Central Asia')",
                "insert into COUNTRIES values ('Bahamas','BS','Caribbean')",
                "insert into COUNTRIES values ('Bangladesh','BD','Asia')",
                "insert into COUNTRIES values ('Barbados','BB','Caribbean')",
                "insert into COUNTRIES values ('Belgium','BE','Europe')",
                "insert into COUNTRIES values ('Belize','BZ','Central America')",
                "insert into COUNTRIES values ('Bermuda','BM','Caribbean')",
                "insert into COUNTRIES values ('Bolivia','BO','South America')",
                "insert into COUNTRIES values ('Botswana','BW','Africa')",
                "insert into COUNTRIES values ('Brazil','BR','South America')",
                "insert into COUNTRIES values ('Bulgaria','BG','Europe')",
                "insert into COUNTRIES values ('Cambodia','KH','Asia')",
                "insert into COUNTRIES values ('Cameroon','CM','Africa')",
                "insert into COUNTRIES values ('Canada','CA','North America')",
                "insert into COUNTRIES values ('Cape Verde','CV','Africa')",
                "insert into COUNTRIES values ('Chile','CL','South America')",
                "insert into COUNTRIES values ('China','CN','Asia')",
                "insert into COUNTRIES values ('Colombia','CO','South America')",
                "insert into COUNTRIES values ('Congo','CG','Africa')",
                "insert into COUNTRIES values ('Costa Rica','CR','Central America')",
                "insert into COUNTRIES values ('Cote d''Ivoire','CI','Africa')",
                "insert into COUNTRIES values ('Cuba','CU','Caribbean')",
                "insert into COUNTRIES values ('Czech Republic','CZ','Europe')",
                "insert into COUNTRIES values ('Denmark','DK','Europe')",
                "insert into COUNTRIES values ('Dominical Republic','DO','Caribbean')",
                "insert into COUNTRIES values ('Ecuador','EC','South America')",
                "insert into COUNTRIES values ('Egypt','EG','North Africa')",
                "insert into COUNTRIES values ('El Salvador','SV','Central America')",
                "insert into COUNTRIES values ('Ethiopia','ET','North Africa')",
                "insert into COUNTRIES values ('Falkland Islands','FK','South America')",
                "insert into COUNTRIES values ('Fiji','FJ','Pacific Islands')",
                "insert into COUNTRIES values ('Finland','FI','Europe')",
                "insert into COUNTRIES values ('France','FR','Europe')",
                "insert into COUNTRIES values ('Georgia','GE','Europe')",
                "insert into COUNTRIES values ('Germany','DE','Europe')",
                "insert into COUNTRIES values ('Ghana','GH','Africa')",
                "insert into COUNTRIES values ('Greece','GR','Europe')",
                "insert into COUNTRIES values ('Guadeloupe','GP','Caribbean')",
                "insert into COUNTRIES values ('Guatemala','GT','Central America')",
                "insert into COUNTRIES values ('Honduras','HN','Central America')",
                "insert into COUNTRIES values ('Hungary','HU','Europe')",
                "insert into COUNTRIES values ('Iceland','IS','Europe')",
                "insert into COUNTRIES values ('India','IN','Asia')",
                "insert into COUNTRIES values ('Indonesia','ID','Asia')",
                "insert into COUNTRIES values ('Iran','IR','Middle East')",
                "insert into COUNTRIES values ('Iraq','IQ','Middle East')",
                "insert into COUNTRIES values ('Ireland','IE','Europe')",
                "insert into COUNTRIES values ('Israel','IL','Middle East')",
                "insert into COUNTRIES values ('Italy','IT','Europe')",
                "insert into COUNTRIES values ('Jamaica','JM','Caribbean')",
                "insert into COUNTRIES values ('Japan','JP','Asia')",
                "insert into COUNTRIES values ('Jordan','JO','Middle East')",
                "insert into COUNTRIES values ('Kenya','KE','Africa')",
                "insert into COUNTRIES values ('Korea, Republic of','KR','Asia')",
                "insert into COUNTRIES values ('Lebanon','LB','Middle East')",
                "insert into COUNTRIES values ('Lithuania','LT','Europe')",
                "insert into COUNTRIES values ('Madagascar','MG','Africa')",
                "insert into COUNTRIES values ('Malaysia','MY','Asia')",
                "insert into COUNTRIES values ('Mali','ML','Africa')",
                "insert into COUNTRIES values ('Mexico','MX','North America')",
                "insert into COUNTRIES values ('Morocco','MA','North Africa')",
                "insert into COUNTRIES values ('Mozambique','MZ','Africa')",
                "insert into COUNTRIES values ('Nepal','NP','Asia')",
                "insert into COUNTRIES values ('Netherlands','NL','Europe')",
                "insert into COUNTRIES values ('New Zealand','NZ','Australia and New Zealand')",
                "insert into COUNTRIES values ('Nicaragua','NI','Central America')",
                "insert into COUNTRIES values ('Nigeria','NG','Africa')",
                "insert into COUNTRIES values ('Norway','NO','Europe')",
                "insert into COUNTRIES values ('Pakistan','PK','Central Asia')",
                "insert into COUNTRIES values ('Paraguay','PY','South America')",
                "insert into COUNTRIES values ('Peru','PE','South America')",
                "insert into COUNTRIES values ('Philippines','PH','Asia')",
                "insert into COUNTRIES values ('Poland','PL','Europe')",
                "insert into COUNTRIES values ('Portugal','PT','Europe')",
                "insert into COUNTRIES values ('Russia','RU','Europe')",
                "insert into COUNTRIES values ('Samoa','WS','Pacific Islands')",
                "insert into COUNTRIES values ('Senegal','SN','Africa')",
                "insert into COUNTRIES values ('Sierra Leone','SL','Africa')",
                "insert into COUNTRIES values ('Singapore','SG','Asia')",
                "insert into COUNTRIES values ('Slovakia','SK','Europe')",
                "insert into COUNTRIES values ('South Africa','ZA','Africa')",
                "insert into COUNTRIES values ('Spain','ES','Europe')",
                "insert into COUNTRIES values ('Sri Lanka','LK','Asia')",
                "insert into COUNTRIES values ('Sudan','SD','Africa')",
                "insert into COUNTRIES values ('Sweden','SE','Europe')",
                "insert into COUNTRIES values ('Switzerland','CH','Europe')",
                "insert into COUNTRIES values ('Syrian Arab Republic','SY','Middle East')",
                "insert into COUNTRIES values ('Tajikistan','TJ','Central Asia')",
                "insert into COUNTRIES values ('Tanzania','TZ','Africa')",
                "insert into COUNTRIES values ('Thailand','TH','Asia')",
                "insert into COUNTRIES values ('Trinidad and Tobago','TT','Caribbean')",
                "insert into COUNTRIES values ('Tunisia','TN','North Africa')",
                "insert into COUNTRIES values ('Turkey','TR','Middle East')",
                "insert into COUNTRIES values ('Ukraine','UA','Europe')",
                "insert into COUNTRIES values ('United Kingdom','GB','Europe')",
                "insert into COUNTRIES values ('United States','US','North America')",
                "insert into COUNTRIES values ('Uruguay','UY','South America')",
                "insert into COUNTRIES values ('Uzbekistan','UZ','Central Asia')",
                "insert into COUNTRIES values ('Venezuela','VE','South America')",
                "insert into COUNTRIES values ('Viet Nam','VN','Asia')",
                "insert into COUNTRIES values ('Virgin Islands (British)','VG','Caribbean')",
                "insert into COUNTRIES values ('Virgin Islands (U.S.)','VI','Caribbean')",
                "insert into COUNTRIES values ('Yugoslavia','YU','Europe')",
                "insert into COUNTRIES values ('Zaire','ZR','Africa')",
                "insert into COUNTRIES values ('Zimbabwe','ZW','Africa')"};
        for(String sql : initQueries) {
            em.createNativeQuery(sql).executeUpdate();
        }
    }

    @Test
    public void testSelectAllCountries(){
        em.getTransaction().begin();
        try {
            List<Country> countries = em.createNativeQuery("select * from COUNTRIES", Country.class).getResultList();
            em.getTransaction().commit();
            Assert.assertFalse(countries.isEmpty());
        }
        catch (Exception e) {
            em.getTransaction().rollback();
            e.printStackTrace();
        }

    }

    @AfterClass
    public static void afterClass(){
        em.createNativeQuery("DROP TABLE COUNTRIES").executeUpdate();
        em.close();
        emf.close();
        em2.close();
        emf2.close();
        //close Derby
        // DriverManager.getConnection("jdbc:derby:;shutdown=true");
    }

}
